package com.yeban.test1.preparestatement;

import com.yeban.test1.entity.Student;
import com.yeban.test1.utils.C3P0ByThreadLocalUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @Desc TODO
 * @Author yeban
 * @Date 2025/11/18
 */
public class StudentDao {
    //需求1：通过姓名模糊查询列表
    public List<Student> select(String query) {
        List<Student> list = new ArrayList<>();
        //1.获得连接对象
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus where name like concat('%',?,'%')";
            ppst = conn.prepareStatement(sql);
            //3.给?占位符传值
            ppst.setString(1, query);
            //4.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //5.处理结果集
            while (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString("id"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                stu.setGrade(rs.getString("grade"));
                String birthdayStr = rs.getString("birthday");
                LocalDate birthday = LocalDate.parse(birthdayStr, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                stu.setBirthday(birthday);
                stu.setPhone(rs.getString("phone"));
                stu.setEmail(rs.getString("email"));
                list.add(stu);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //6.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
        return list;
    }

    public void selectName(String id) {
        //1.获得连接对象
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1, id);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            if (rs.next()) {
                System.out.println("查询成功");
                //4.处理结果集
                String name = rs.getString("name");
                String grade = rs.getString("grade");
                System.out.println(id + "号的学生姓名为:" + name + "，所在年级:" + grade);

            } else {
                System.out.println("查询失败");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //5.关闭资源
            C3P0ByThreadLocalUtils.close();
        }

    }

    public List<Student> selectAll() {
        List<Student> list = new ArrayList<>();
        //1.获得连接对象
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus ";
            ppst = conn.prepareStatement(sql);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString("id"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                stu.setGrade(rs.getString("grade"));
                String birthdayStr = rs.getString("birthday");
                LocalDate birthday = LocalDate.parse(birthdayStr, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                stu.setBirthday(birthday);
                stu.setPhone(rs.getString("phone"));
                stu.setEmail(rs.getString("email"));
                list.add(stu);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //5.关闭资源
            C3P0ByThreadLocalUtils.close();
        }
        return list;
    }

    public void sum() {
        List<Student> list = selectAll();
        System.out.println("学生总人数" + list.size());
    }

    public int update(String id, String birthdayStr) {
        Connection conn = null;
        PreparedStatement ppst = null;
        try {
            // 1.获取数据库连接
            conn = C3P0ByThreadLocalUtils.getConn();
            // 2.预编译UPDATE语句
            String sql = new String("UPDATE stus SET birthday = ? where id = ? ");
            ppst = conn.prepareStatement(sql);
            LocalDate birthday1 = LocalDate.parse(birthdayStr, DateTimeFormatter.ofPattern("yyyy-MM-dd "));
            String birthday = String.valueOf(birthday1);
            ppst.setString(1, birthday);
            ppst.setString(2, id);
            return ppst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("根据学号更新学生信息失败", e);
        } finally {
            // 5.关闭资源（无论成功失败都要关闭）
            C3P0ByThreadLocalUtils.close();
        }
    }

    public int delete(String id) {
        //1.获取连接对象
        Connection conn = C3P0ByThreadLocalUtils.getConn();
        PreparedStatement ppst = null;
        //2.预编译SQL，获取语句对象
        try {
            String sql = "delete from stu where id = ?";
            ppst = conn.prepareStatement(sql);
            //3.给?占位符传值
            ppst.setString(1, id);
            //4.执行 增、删、改 操作
            int i = ppst.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            C3P0ByThreadLocalUtils.close();
        }

    }
}
